package dbLayer;

import modelLayer.Subject;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

public class DBSubject {
	
	private Connection con;
	
	public DBSubject () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public Subject findSubjectID (int ID) {
		
		ResultSet results;
		Subject subject = null;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Subjects WHERE ID = ?");
			statement.setInt(1, ID);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				subject = buildObject(results);
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: "+e);
		}
		
		return subject;
		
	}
	
	public List<Subject> findSubjectsName (String name) {
		
		ResultSet results;
		List<Subject> matchingSubjects = new ArrayList<>();
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Subjects WHERE Name like ?");
			statement.setString(1, "%" + name + "%");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while (results.next()) {
				Subject subject = new Subject();
				subject = buildObject(results);
				System.out.println(subject);
				matchingSubjects.add(subject);
			}
			statement.close();
		}
		catch (Exception e) {
			System.out.println("Query exception: "+e);
		}
		
		return matchingSubjects;
	}
	
	public List<Subject> allSubjects () {
		
		List<Subject> allSubjects = new ArrayList<>();
		
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Subjects");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				Subject s = new Subject();
				s = buildObject(results);
				allSubjects.add(s);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return allSubjects;
		
	}
	
	public void addSubject (Subject subject) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("INSERT INTO Subjects VALUES (?)");
			statement.setString(1, subject.getName());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}

	public int highestID() {
		
		int maxID = 0;
		
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT MAX(ID) FROM Subjects");
			statement.setQueryTimeout(5);
			
			results = statement.executeQuery();
			
			while (results.next()) {
				
				maxID = results.getInt(1);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return maxID;
		
	}

	public void updateSubject(Subject subject) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Subjects SET Name = ? WHERE ID = ?");
			statement.setString(1, subject.getName());
			statement.setInt(2, subject.getID());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}

	public void removeSubject(int subjectID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("DELETE FROM Subjects WHERE ID = ?");
			statement.setInt(1, subjectID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}
	
	private Subject buildObject(ResultSet results) {
		
		Subject subject = null;
		
		try {
			subject = new Subject (results.getInt("ID"),
									results.getString("Name"));
		}
		catch (Exception e) {
			System.out.println("Creation of the subject caused exception: " + e);
		}
		
		return subject;
	}
	
}
